package com.apes.fn.server.workDesk.smartHelper.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.fn.scm.report.util.SalesReportUtils;
import com.apes.framework.query.ApesDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * @program: apes-server
 * @description:
 * @author: czs
 * @create: 2023-06-15 11:14
 **/
@Component
public class SmartHelperDao extends ApesDao {

    /**
     * 查询在店车辆
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStoreCar(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append("select (case c.ID " +
                "    when 'ZK000754' then '在店车辆' " +
                "    when 'ZK000752' then '待开单' " +
                "    when 'ZK000755' then '内协车辆' " +
                "    else '待交车' " +
                "    end) name,(case c.ID " +
                "    when 'ZK000754' then 0 " +
                "    when 'ZK000752' then 10 " +
                "    when 'ZK000755' then 20 " +
                "    else 30 " +
                "    end) sort,C.id id,count(1) count,'black' color " +
                "from VEHICLE_CONTROL A ,FSM_FLOW_INST b,FSM_STATE c " +
                "where a.STATE <>'ZK000758' " +
                "and a.id =b.BUSINESS_KEY " +
                "and b.CURRENT_STATE =c.id " +
                "and c.id in ('ZK000754','ZK000752','ZK000755','ZK000753') " +
                "and b.FSM_FLOW_ID='000583' "  );
        SalesReportUtils.appendCondition(sf,param,"A.","DEPT_ID");
        sf.append("group by C.id");

        return toJSONArray(find(sfToString(sf), param));
    }
    /**
     * 查询询价单据
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStoreInquiryPrice(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        if (jsonObject.get("DEPT_ID")!=null)jsonObject.put("BELONG_DEPT_ID",jsonObject.get("DEPT_ID"));
        StringBuffer sf = new StringBuffer();
        sf.append("select name,id,count(1)count ,'black' color,sort " +
                "from (select (case A.DJZT when 40 then '询价退回' else '询价中' end) name," +
                "     (case A.DJZT when 40 then 20 else 10 end) sort, " +
                "             (case A.DJZT when 40 then 'XJTH' else 'XJZ' end)  id " +
                "      FROM SER_SERVICE_PLAN B, " +
                "           PUR_SPXJD A " +
                "      where A.LYDJHM = B.ID " +
                "        and A.DJZT IN (0, 50, 30, 40) " );
        SalesReportUtils.appendCondition(sf,param,"B.","BELONG_DEPT_ID");

        sf.append("    ) " +
                "group by name,id,sort "
        );

        return toJSONArray(find(sfToString(sf), jsonObject));
    }
    /**
     * 查询方案状态数量
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStoreServicePlanState(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        if (jsonObject.get("DEPT_ID")!=null)jsonObject.put("BELONG_DEPT_ID",jsonObject.get("DEPT_ID"));
        StringBuffer sf = new StringBuffer();
        sf.append("select STATE id,decode(GET_SERVICEPLAN_STATE(STATE),'转价格审核','审核中','质检','待收款','价格审核退回','退回',GET_SERVICEPLAN_STATE(STATE)) name," +
                "   decode(GET_SERVICEPLAN_STATE(STATE),'转价格审核',0,'质检',1,'价格审核退回',10,GET_SERVICEPLAN_STATE(STATE)) sort" +
                "   ,count(1)count, " +
                "       max(CREATE_DATE)gtCreateDate,min(CREATE_DATE)ltCreateDateSQL," +
                "(case GET_SERVICEPLAN_STATE(STATE) when '质检' then 'orange' else 'black' end) color " +
                "from SER_SERVICE_PLAN A " +
                "where RECEIVABLE_DATE is null  " );
        SalesReportUtils.appendCondition(sf,param,"A.",true,"STATE");
        SalesReportUtils.appendCondition(sf,param,"A.","BELONG_DEPT_ID");
        sf.append("group by STATE");
        return toJSONArray(find(sfToString(sf), jsonObject));
    }

    /**
     * 查询门店所有服务
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStorePlanDetail(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        if (jsonObject.get("DEPT_ID")!=null)jsonObject.put("BELONG_DEPT_ID",jsonObject.get("DEPT_ID"));
        StringBuffer sf = new StringBuffer();
        sf.append("select COUNT(1) count,(case" +
                " ORDER_TYPE when '服务方案' then '方案单' " +
                "  when '定损报价单' then '定损单' " +
                "  when '车险报案单' then '车险报案' " +
                " end) name,(case" +
                " ORDER_TYPE when '服务方案' then 0 " +
                "  when '定损报价单' then 20 " +
                "  when '车险报案单' then 30 " +
                " end) sort, (case" +
                " ORDER_TYPE when '服务方案' then 'Service' " +
                "  when '定损报价单' then 'LossQuotation' " +
                "  when '车险报案单' then 'CarAccident' " +
                " end) " +
                "id ,max(CREATE_DATE)gtCreateDate,min(CREATE_DATE)ltCreateDateSQL " +
                "from V_UNCOMPLETED_ORDER A " +
                "where  ORDER_TYPE in ('服务方案','定损报价单','车险报案单') " );
        SalesReportUtils.appendCondition(sf,param,"A.","DEPT_ID");
        sf.append(
                "GROUP BY ORDER_TYPE " +
                "union all " +
                "select count(1),'未确认',10 sort,'ServiceNotConfirm' id,max(CREATE_DATE)gtCreateDate,min(CREATE_DATE)ltCreateDateSQL " +
                "from SER_SERVICE_PLAN A " +
                "where CONFIRM_DATE is null " +
                "  and RECEIVABLE_DATE is null" +
                " and STATE = 'submission'" );
        SalesReportUtils.appendCondition(sf,param,"A.","BELONG_DEPT_ID");
        return toJSONArray(find(sfToString(sf), jsonObject));
    }

    /**
     * 查询门店施工单据
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStoreWorking(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        if (jsonObject.get("DEPT_ID")!=null)jsonObject.put("BELONG_DEPT_ID",jsonObject.get("DEPT_ID"));
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT count(DISTINCT (A.id))        count, " +
                "       min(A.CREATE_DATE) ltCreateDateSQL, " +
                "       max(A.CREATE_DATE) gtCreateDate, " +
                "       (case B.WORK_STATE " +
                "           when 'wait' then '待施工' " +
                "           when 'pause' then '暂停施工' " +
                "           when 'working' then '施工中' " +
                "           end)name,(case B.WORK_STATE " +
                "           when 'wait' then 0 " +
                "           when 'pause' then 20 " +
                "           when 'working' then 10 " +
                "           end)sort, " +
                "       B.WORK_STATE id,'black' color " +
                "FROM SER_SERVICE_PLAN A " +
                "         LEFT JOIN SER_SERVICE_PLAN_ITEM B ON A.ID = B.SERVICE_PLAN_ID " +
                "         LEFT JOIN SER_MAINTENANCE_ORDER C ON B.ID = C.service_plan_item " +
                "WHERE nvl(C.BUSINESS_TYPE,'out') <> 'out' " +
                "  AND A.RECEIVABLE_DATE IS NULL " +
                "      AND A.STATE NOT IN('receivables','termination') " +
                "  AND A.CONFIRM_DATE IS NOT NULL " +
                "  AND A.TERMINATION_DATE IS NULL " +
                "      and B.WORK_STATE in ('wait','pause','working') " +
                "  AND C.STATE = 'create'" );
        SalesReportUtils.appendCondition(sf,param,"A.","BELONG_DEPT_ID");

        sf.append("group by B.WORK_STATE");
        return toJSONArray(find(sfToString(sf), jsonObject));
    }

    /**
     * 查询门店待质检单据
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStoreWaitQualityTest(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        if (jsonObject.get("DEPT_ID")!=null)jsonObject.put("BELONG_DEPT_ID",jsonObject.get("DEPT_ID"));
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT count(DISTINCT (A.id))        count, " +
                "       min(A.CREATE_DATE) ltCreateDateSQL, " +
                "       max(A.CREATE_DATE) gtCreateDate, " +
                "       '待质检'name,10 sort, " +
                "       'waitQualityTest' id,'black' color " +
                "FROM SER_SERVICE_PLAN A " +
                "         LEFT JOIN SER_SERVICE_PLAN_ITEM B ON A.ID = B.SERVICE_PLAN_ID " +
                "         LEFT JOIN SER_MAINTENANCE_ORDER C ON B.ID = C.service_plan_item " +
                "WHERE  A.RECEIVABLE_DATE IS NULL " +
                "      AND A.STATE = 'confirm' " +
                "  AND C.WORK_STATE = 'finish' " +
                "  AND A.CONFIRM_DATE IS NOT NULL " +
                "  AND A.TERMINATION_DATE IS NULL " +
                "" );
        SalesReportUtils.appendCondition(sf,param,"A.","BELONG_DEPT_ID");

        return toJSONArray(find(sfToString(sf), jsonObject));
    }

    /**
     * 查询门店要货
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStoreRequireDetail(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        if (jsonObject.get("DEPT_ID")!=null)jsonObject.put("BELONG_DEPT_ID",jsonObject.get("DEPT_ID"));
        StringBuffer sf = new StringBuffer();
        sf.append("select count(1) count,name,'black' color,sid id,sort " +
                "from (SELECT A.ID, P.ID PID, E.ID EID,(case " +
                "    when P.STATE = 'create' then '未审核' " +
                "    when P.STATE in ('repeal','approve')  and (D.QTY_REQUIRE - D.QTY_ENDED > D.QTY_ASSIGNED) then '未配货' " +
                "    when P.STATE in ('repeal','approve') AND ( D.QTY_ASSIGNED > D.QTY_ARRIVE) then '未到货' " +
                "    end) name,(case " +
                "    when P.STATE = 'create' then 0 " +
                "    when P.STATE in ('repeal','approve')  and (D.QTY_REQUIRE - D.QTY_ENDED > D.QTY_ASSIGNED) then 10 " +
                "    when P.STATE in ('repeal','approve') AND ( D.QTY_ASSIGNED > D.QTY_ARRIVE) then 20 " +
                "    end) sort,(case " +
                "    when P.STATE = 'create' then 'requireUnAudit' " +
                "    when P.STATE in ('repeal','approve')  and (D.QTY_REQUIRE - D.QTY_ENDED > D.QTY_ASSIGNED) then 'requireUnAssigned' " +
                "    when P.STATE in ('repeal','approve') AND ( D.QTY_ASSIGNED > D.QTY_ARRIVE) then 'requireUnDelivery' " +
                "    end) sid " +
                "      FROM ser_service_plan A " +
                "               left join " +
                "           stock_product_require P ON A.id = P.Source " +
                "               left join STOCK_PRODUCT_REQUIRE_ITEM D ON P.ID = D.Product_Require_Id " +
                "               left join MAS_PRODUCT E ON D.PRODUCT_ID = E.ID " +
                "               left join STOCK_PRODUCT_REQUIRE_ITEM F ON F.PRODUCT_REQUIRE_ID = P.ID " +
                "      where F.ID IS NOT NULL " +
                "            and p.STATE in ('create','repeal','approve') " +
                "" );
        SalesReportUtils.appendCondition(sf,param,"A.","BELONG_DEPT_ID");

        sf.append(") A where name is not null " +
                "group by name,sid,sort");
        return toJSONArray(find(sfToString(sf), jsonObject));
    }
    /**
     * 查询门店待领用
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStoreWaitReceive(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        if (jsonObject.get("DEPT_ID")!=null)jsonObject.put("BELONG_DEPT_ID",jsonObject.get("DEPT_ID"));
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT COUNT(ID) count,'待领用' name,'waitReceive' id,0 sort, MIN(CREATE_DATE) ltCreateDateSQL, MAX(CREATE_DATE) gtCreateDate, " +
                "'black' color " +
                "FROM (SELECT B.ID, " +
                "             b.CREATE_DATE " +
                "      FROM SER_SERVICE_PLAN_ITEM A " +
                "               LEFT JOIN SER_SERVICE_PLAN B " +
                "                         ON A.SERVICE_PLAN_ID = B.ID " +
                "               LEFT JOIN MAS_PRODUCT D " +
                "                         ON A.PRODUCT_ID = D.ID " +
                "      WHERE D.PRODUCT_GROUP_ID = 10 " +
                "        AND A.QTY_RECEIVE < A.SALE_QTY " +
                "        AND B.STATE = 'confirm' " +
                "        and a.QUALITY_TEST_DATE is null " +
                "       " +
                "" );
        SalesReportUtils.appendCondition(sf,param,"B.","BELONG_DEPT_ID");

        sf.append("GROUP by B.ID, b.CREATE_DATE)");
        return toJSONArray(find(sfToString(sf), jsonObject));
    }
    /**
     * 查询门店调拨
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStoreTransfer(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append("select (case OPERATION when 'receipt' then '未收货' ELSE '未发货' end)name," +
                "   (case OPERATION when 'receipt' then 0 ELSE 10 end)sort, " +
                "       (case OPERATION when 'receipt' then '1' ELSE '0' end)CARGO_STATE, " +
                "       OPERATION id,count(1)COUNT,'black' color " +
                "from STOCK_PICK A ,mas_location b,MAS_DEPOT c " +
                "where OPERATION <>'done' " +
                "and a.LOCATION_ID = b.ID " +
                " and c.id = b.DEPOT_ID " +
                "and a.STATE  IN('confirmed') " +
                "" );
        SalesReportUtils.appendCondition(sf,param,"C.","DEPT_ID");

        sf.append("group by OPERATION");
        return toJSONArray(find(sfToString(sf), param));
    }
    /**
     * 查询门店待回访
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryInStoreWaitRevisit(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append("select count(1)COUNT,'待回访' name,'WaitRevisit' id,0 sort,'orange' color " +
                "from FN_RETURN_VISIT A " +
                "where STATE='create' " +
                "" );
        SalesReportUtils.appendCondition(sf,param,"A.","DEPT_ID");

//        sf.append("group by OPERATION");
        return toJSONArray(find(sfToString(sf), param));
    }
    /**
     * 查询门店待回访
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryOutStoreLeaveWithoutPaying(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        if (jsonObject.get("DEPT_ID")!=null)jsonObject.put("BELONG_DEPT_ID",jsonObject.get("DEPT_ID"));
        StringBuffer sf = new StringBuffer();
        sf.append("select COUNT(distinct (A.id)) count, '未结算交车' name,0 sort, 'orange' color, 'LeaveWithoutPaying' id " +
                "FROM SER_SERVICE_PLAN A " +
                "         LEFT JOIN SER_SERVICE_PLAN_ITEM B ON A.ID = B.SERVICE_PLAN_ID " +
                "         LEFT JOIN " +
                "     SER_MAINTENANCE_ORDER C ON B.ID = C.service_plan_item " +
                "WHERE A.STATE IN ('confirm', 'qualityTest') " +
                "  and not exists(select * from FN_PICK_UP_CAR s where s.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID and s.STATE = 'approve' " +
                "      ) " +
                "  AND C.WORK_START_TIME IS NOT NULL" );
        SalesReportUtils.appendCondition(sf,jsonObject,"A.","BELONG_DEPT_ID");

        return toJSONArray(find(sfToString(sf), jsonObject));
    }
    /**
     * 查询门店资金收入单
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryFinanceMoneyIncome(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        StringBuffer sf = new StringBuffer();
        sf.append("select count,id,'未审核' name,0 sort,'black' color " +
                "from (select count(1)count, 'moneyIncomeNotApply' id  " +
                "      from MONEY_INCOME A " +
                "      where STATE = 'sent'" );
        SalesReportUtils.appendCondition(sf,jsonObject,"A.","DEPT_ID");
        sf.append(")");
        return toJSONArray(find(sfToString(sf), jsonObject));
    }
    /**
     * 查询门店退款单据
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryFinanceOrderRefund(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        StringBuffer sf = new StringBuffer();
        sf.append("select count(1)count,(case ORDER_TYPE when 'RetailReturn.orderType.A' then '有单退货' else '无单退货' end)name," +
                "(case ORDER_TYPE when 'RetailReturn.orderType.A' then 0 else 10 end)sort, " +
                "       ORDER_TYPE id,'black' color " +
                "from RETAIL_RETURN A " +
                "where STATE in ('sent','confirmed')" );
        SalesReportUtils.appendCondition(sf,jsonObject,"A.","DEPT_ID");
        sf.append("group by ORDER_TYPE");
//        sf.append("group by ORDER_TYPE " +
//                "union all " +
//                "select COUNT(1),'分销退货' name,'saleReturn' id,'black' color " +
//                "from sale_return A " +
//                "where STATE in ('sent','confirmed')");
//        SalesReportUtils.appendCondition(sf,jsonObject,"A.","DEPT_ID");
        return toJSONArray(find(sfToString(sf), jsonObject));
    }
    /**
     * 查询门店结算单
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryFinanceSettlementOrder(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        StringBuffer sf = new StringBuffer();
        sf.append("select a.*,'black' color " +
                "from (SELECT count(T.ID)            count, " +
                "             '对账单1.0'               name," +
                "               0               sort, " +
                "             'merchantSettlementV1' id " +
                "      FROM FN_MERCHANT_SETTLEMENT T " +
                "      WHERE T.STATE NOT IN ('done', 'payment', 'squareAccounts')" );
        if ( jsonObject.get("DEPT_ID")!=null)sf.append(" and EXISTS(SELECT 1 FROM MAS_DEPT S WHERE S.ID = :DEPT_ID AND T.MERCHANT_ID = S.COMPANY_ID) ");

        sf.append("  union all " +
                "      SELECT count(T.ID), " +
                "             '对账单2.0' name, " +
                "             10, " +
                "             'merchantSettlementV2' " +
                "      FROM MERCHANT_ACCOUNT_SETTLEMENT T " +
                "      WHERE T.STATE NOT IN ('squareAccounts', 'done', 'payment')");
        if ( jsonObject.get("DEPT_ID")!=null)sf.append(" and EXISTS(SELECT 1 FROM MAS_DEPT S WHERE S.ID = :DEPT_ID AND T.MERCHANT_ID = S.COMPANY_ID) ");

        sf.append(" " +
                "      union all " +
                "      SELECT sum(UN_SETTLE_AMOUNT), '挂账应收' name," +
                "      20," +
                "        'xsInvoice' id " +
                "      FROM V_XS_INVOICE A " +
                "      WHERE 1 = 1 " +
                "        and SETTLED_QTY = 0");
        SalesReportUtils.appendCondition(sf,jsonObject,"A.","DEPT_ID");

        sf.append("  union all " +
                "      select count(1), " +
                "             '客户结算' name, " +
                "             30, " +
                "             'ClientSettle' " +
                "      FROM ACC_SETTLE t " +
                " WHERE T.STATE <> 'executive' " +
                "   and STATE in ('sent', 'approve') " +
                "   AND SETTLE_TYPE = 'Customer' ");
        SalesReportUtils.appendCondition(sf,jsonObject,"T.","DEPT_ID");
        sf.append("       union all " +
                "      select count(1), '发票申请' name,40 , 'invoiceApply' id " +
                "      FROM ACC_INVOICE_APPLY t " +
                "      WHERE t.STATE = 'sent' ");
        if ( jsonObject.get("DEPT_ID")!=null)sf.append(" and EXISTS(SELECT 1 FROM MAS_DEPT S WHERE S.ID = :DEPT_ID AND T.COMPANY_ID = S.COMPANY_ID) ");
        sf.append("      union all " +
                "      select count(1), '发票申请|直营' name,50, 'invoiceApplyByDirect' id " +
                "      FROM ACC_DIRECT_INVOICE_APPLY t " +
                "      WHERE t.STATE = 'sent'");
        SalesReportUtils.appendCondition(sf,jsonObject,"T.","DEPT_ID");
        sf.append(") A");
        sf.append(" where A.id not in ('merchantSettlementV1','merchantSettlementV2','xsInvoice','ClientSettle','invoiceApply')");

        return toJSONArray(find(sfToString(sf), jsonObject));
    }
    /**
     * 查询门店人员调动
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryPersonRemove(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        StringBuffer sf = new StringBuffer();
        sf.append("select count(1) count,'入职申请单' name,0 sort,'applyJobOrder' id,'black' color " +
                "from hr_Apply_Job_Order a " +
                "where STATE in('create','firstTrial','return')" );
//        SalesReportUtils.appendCondition(sf,jsonObject,"A.","DEPT_ID");
        if (jsonObject.get("DEPT_ID")!=null)sf.append(" and exists(select 1 from MAS_DEPT s where s.id = :DEPT_ID and s.BRANCH_ID = a.BRANCH_ID) ");
        sf.append("union all " +
                "select count(1) count,'调动/支援' name,10,'transferApplication' id,'black' color " +
                "from hr_transfer_application A " +
                "where STATE='create'" );
        SalesReportUtils.appendCondition(sf,jsonObject,"A.","DEPT_ID");
        sf.append("union all " +
                "select count(1) count,'计费支援' name,20,'supportApplication' id,'black' color " +
                "from hr_support_application A " +
                "where STATE='create'");
        SalesReportUtils.appendCondition(sf,jsonObject,"A.","DEPT_ID");
        return toJSONArray(find(sfToString(sf), jsonObject));
    }
    /**
     * 查询门店人员资料
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray queryPersonFile(JSONObject param) {
        JSONObject jsonObject = new JSONObject(param);
        StringBuffer sf = new StringBuffer();
        sf.append("select count(1) count,'员工资料补充' name,0 sort,'entryInformation' id,'black' color " +
                "from hr_entry_information A " +
                "where STATE='create'" );
        if ( jsonObject.get("DEPT_ID")!=null)sf.append(" and exists(select 1 from MAS_DEPT s where s.id = :DEPT_ID and s.BRANCH_ID = a.BRANCH_ID) ");
        return toJSONArray(find(sfToString(sf), jsonObject));
    }
    public String sfToString( StringBuffer sf){
        String string = sf.toString();
        return "select a.* " +
                "from ("+string+") A " +
                "where count>0" +
                " order by sort ";
    }
}
